<?php
require_once('application/libraries/show.php');
require_once('application/libraries/person.php');
require_once('application/libraries/crewMember.php');
require_once('application/libraries/castMember.php');
class ShowDAL extends CI_Model{
	private $showTableName = "shows";
	private $personTableName = "persons";
	public function addShow($show){
		$sql = "INSERT INTO $this->showTableName (name, startyear, endyear, description) VALUES(?, ?, ?, ?)";
		$query = $this->db->query($sql, array($show->getName(), $show->getStartyear(), $show->getEndyear(), $show->getDescription()));
		return $query;
	}
	public function deleteShow($show){
		$sql = "DELETE FROM $this->showTableName WHERE name = ?";
		$query = $this->db->query($sql, array($show->getName()));
		return $query;
	}	
	public function showExists($show){
		// Returns true if show exists
		$name = $show->getName();
		$sql = "SELECT * FROM $this->showTableName WHERE name = ?";
		$query = $this->db->query($sql, array($show->getName()));
		if($query->num_rows == 1){
			return TRUE;
		}
		else return FALSE;
	}
	public function getShowByID($id){
		// Returns show if it exists
		$sql = "SELECT * FROM $this->showTableName WHERE id = ?";
		$query = $this->db->query($sql, array($id));
		if ($query->num_rows() > 0){
 		  $row = $query->row(); 
 		  $params = array(
 		  	ID => $row->id,
 		  	NAME => $row->name,
 		  	START_YEAR => $row->startYear,
 		  	END_YEAR => $row->endYear,
 		  	DESCRIPTION => $row->description
 		  );
 		  $show = new Show($params);
 		  return $show;
		}
	}
	public function getShowID($name){
		// Return showID
		$sql = "SELECT * FROM $this->showTableName WHERE name = ?";
		$query = $this->db->query($sql, array($name));
		if ($query->num_rows() > 0){
 		  $row = $query->row(); 
 		  $showID = $row->id;
 		  return $showID;
		}
	}
	public function getAllShows(){
		// Get all shows from database
		$sql = "SELECT * FROM $this->showTableName";
		$query = $this->db->query($sql);
		$shows = array();
		// Foreach resultrow create new showobject and save to shows array
		foreach ($query->result_array() as $row)
		{
			$params = array(
				ID => $row['id'],
				NAME => $row['name'],
				START_YEAR => $row['startYear'],
				END_YEAR => $row['endYear'],
				DESCRIPTION => $row['description']
			);
			$show = new Show($params);
			$shows[] = $show;
		}
		return $shows;
	}
	public function addPerson($person){
		$firstname = $person->getFirstname();
		$lastname = $person->getLastname();
		$description = $person->GetDescription();		
		$sql = "INSERT INTO $this->personTableName(firstname, lastname, description) VALUES(?, ?, ?)";
		$query = $this->db->query($sql, array($firstname, $lastname, $description));
		return $query;
	}
	public function deletePerson($person){
		$firstname = $person->GetFirstname();
		$lastname = $person->GetLastname();
		$sql = "DELETE FROM $this->personTableName WHERE (firstname = ? AND lastname = ?)";
		$query = $this->db->query($sql, array($firstname, $lastname));
		return $query;

	}	
	public function personExists($person){
		// Returns true if person exists
		$firstname = $person->GetFirstname();
		$lastname = $person->GetLastname();
		$sql = "SELECT * FROM $this->personTableName WHERE (firstname = ? AND lastname = ?)";
		$query = $this->db->query($sql, array($firstname, $lastname));
		if($query->num_rows == 1){
			return TRUE;
		}
		else return FALSE;
	}
	public function getPersonByID($id){
		// Returns person if it exists
		$sql = "SELECT * FROM $this->personTableName WHERE id = ?";
		$query = $this->db->query($sql, array($id));
		if ($query->num_rows() > 0){
 		  $row = $query->row(); 
 		  $params = array(
 		  	ID => $row->id,
 		  	FIRSTNAME => $row->firstname,
 		  	LASTNAME => $row->lastname,
 		  	DESCRIPTION => $row->description
 		  );
 		  $person = new Person($params);
 		  return $person;
		}
	}
	public function getPersonID($firstname, $lastname){
		// Return personID
		$sql = "SELECT * FROM $this->personTableName WHERE firstname = ? AND lastname = ?";
		$query = $this->db->query($sql, array($firstname, $lastname));
		if ($query->num_rows() > 0){
 		  $row = $query->row(); 
 		  $showID = $row->id;
 		  return $showID;
		}
	}
	public function getAllPersons(){
		// Get all persons from database
		$sql = "SELECT * FROM $this->personTableName";
		$query = $this->db->query($sql);
		$persons = array();
		// Foreach resultrow create new person object and save to persons array
		foreach ($query->result_array() as $row){
			$params = array(
				ID => $row['id'],
				FIRSTNAME => $row['firstname'],
				LASTNAME => $row['lastname'],
				DESCRIPTION => $row['description']
			);			
			$person = new Person($params);
			$persons[] = $person;
		}
		return $persons;
	}
	public function getCrewByShowID($showID){
		$sql = "SELECT * FROM crew WHERE showID = ?";
		$query = $this->db->query($sql, array($showID));
		$crew = array();
		foreach ($query->result_array() as $row){
			$params = array(
				ID => $row['id'],
				PERSON_ID => $row['personID'],
				SHOW_ID => $row['showID'],
				TYPE => $row['type']
			);		
			$crewMember = new CrewMember($params);
			$crew[] = $crewMember;
		}
		return $crew;
	}
	public function getCastByShowID($showID){
		$sql = "SELECT * FROM cast WHERE showID = ? ORDER BY importance";
		$query = $this->db->query($sql, array($showID));
		$cast = array();
		foreach ($query->result_array() as $row){
			$params = array(
				ID => $row['id'],
				PERSON_ID => $row['personID'],
				SHOW_ID => $row['showID'],
				CHARACTER_NAME => $row['characterName'],
			);	
			$castMember = new CastMember($params);
			$cast[] = $castMember;
		}
		return $cast;
	}
	public function getActingJobs($personID){
		// Get shows that this person has acted in
		$sql = "SELECT * FROM cast WHERE personID = ?";
		$query = $this->db->query($sql, array($personID));
		$cast = array();
		foreach ($query->result_array() as $row){
			$params = array(
				ID => $row['id'],
				PERSON_ID => $row['personID'],
				SHOW_ID => $row['showID'],
				CHARACTER_NAME => $row['characterName'] 
			);
			$castMember = new CastMember($params);
			$cast[] = $castMember;
		}
		return $cast;
	}
	public function getCrewJobs($personID){
		// Get shows this person has worked on
		$sql = "SELECT * FROM crew WHERE personID = ?";
		$query = $this->db->query($sql, array($personID));
		$crew = array();
		foreach ($query->result_array() as $row)
		{
			$params = array(
				ID => $row['id'],
				PERSON_ID => $row['personID'],
				SHOW_ID => $row['showID'],
				TYPE => $row['type']
			);
			$crewMember = new CrewMember($params);
			$crew[] = $crewMember;
		}
		return $crew;
	}
}